package com.logos.jdbc.base;

import org.junit.Test;

import java.sql.*;

public class JDBCOperation {

    public static final String url = "jdbc:mysql://192.168.227.133:3306/jdbcTest";

    public static final String userName = "root";

    public static final String passWord = "123456";

    @Test
    public void testQuerySingleRowAndCol() throws Exception {
        //1. 注册驱动
        //2. 获取连接对象
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //3. 获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select count(*) as count from emp");
        ResultSet resultSet = preparedStatement.executeQuery();

        //5. 得到结果集
        while (resultSet.next()) {
            int count = resultSet.getInt(1);
            //int count = resultSet.getInt("count");
            System.out.println("count:" + count);
        }
        //6. 关闭资源(先开后关)
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testQuerySingleRowAndCols() throws Exception {
        //1. 注册驱动
        //2. 获取连接对象
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //3. 获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select emp_id, emp_name, emp_salary, emp_age from emp where emp_id = ?");

        preparedStatement.setInt(1, 5);
        ResultSet resultSet = preparedStatement.executeQuery();

        //5. 得到结果集
        while (resultSet.next()) {
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            System.out.println(empId + "\t" + empName + "\t" + empSalary + "\t" + empAge);
        }
        //6. 关闭资源(先开后关)
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testQueryRowsAndCols() throws Exception {
        //1. 注册驱动
        //2. 获取连接对象
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //3. 获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select emp_id, emp_name, emp_salary, emp_age from emp where emp_age > ?");

        // 为占位符赋值
        preparedStatement.setInt(1, 18);
        //4. 执行sql语句并得到结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        //5. 解析结果集
        while (resultSet.next()) {
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            System.out.println(empId + "\t" + empName + "\t" + empSalary + "\t" + empAge);
        }
        //6. 关闭资源(先开后关)
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }


    @Test
    public void testInsert() throws Exception {
        //1. 注册驱动
        //2. 获取连接对象
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //3. 获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("insert into emp(emp_name, emp_salary, emp_age) VALUES (?,?,?)");

        // 为占位符赋值
        preparedStatement.setString(1, "rose");
        preparedStatement.setDouble(2, 345.67);
        preparedStatement.setInt(3, 19);
        //4. 执行sql语句并得到结果集
        int result = preparedStatement.executeUpdate();

        //5. 解析结果集
        if (result > 0) {
            System.out.println("success!");
        } else {
            System.out.println("fail");
        }
        //6. 关闭资源(先开后关)
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate() throws Exception {
        //1. 注册驱动
        //2. 获取连接对象
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //3. 获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("update emp set emp_salary = ? where emp_name = ?");

        //为占位符赋值
        preparedStatement.setDouble(1, 888.88);
        preparedStatement.setString(2, "rose");
        //4. 执行sql语句并得到结果集
        int result = preparedStatement.executeUpdate();

        //5. 解析结果集
        if (result > 0) {
            System.out.println("success!");
        } else {
            System.out.println("fail");
        }
        //6. 关闭资源(先开后关)
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testDelete() throws Exception {
        //1. 注册驱动
        //2. 获取连接对象
        Connection connection = DriverManager.getConnection(url, userName, passWord);
        //3. 获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("delete from emp where emp_name = ?");

        //为占位符赋值
        preparedStatement.setString(1, "rose");
        //4. 执行sql语句并得到结果集
        int result = preparedStatement.executeUpdate();

        //5. 解析结果集
        if (result > 0) {
            System.out.println("success!");
        } else {
            System.out.println("fail");
        }
        //6. 关闭资源(先开后关)
        preparedStatement.close();
        connection.close();
    }
}
